﻿CREATE FUNCTION [sp_ssiscatalog].[get_status_from_status_desc]
(
	@status_desc	NVARCHAR(MAX)
)
RETURNS TABLE AS 
		RETURN
		SELECT	[status_id]	=	CONVERT(INT,				CASE	WHEN	@status_desc	=	N'created'				THEN	1
																	WHEN	@status_desc	=	N'running'				THEN	2
																	WHEN	@status_desc	=	N'canceled'				THEN	3
																	WHEN	@status_desc	=	N'failed'				THEN	4
																	WHEN	@status_desc	=	N'pending'				THEN	5
																	WHEN	@status_desc	=	N'ended unexpectedly'	THEN	6
																	WHEN	@status_desc	=	N'succeeded'			THEN	7
																	WHEN	@status_desc	=	N'stopping'				THEN	8
																	WHEN	@status_desc	=	N'completed'			THEN	9
																	ELSE	NULL
													END
								)